from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from passwd import dburi
import json

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = dburi
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy()
db.init_app(app)

# Models #####
class Checkin(db.Model):
    __tablename__ = 'checkin'
    business_id = db.Column(db.String(50), primary_key=True)
    date = db.Column(db.Text)

    def __init__(self, business_id, date):
        self.business_id = business_id
        self.date = date

class Review(db.Model):
    __tablename__ = 'review'
    review_id = db.Column(db.String(50), primary_key=True)
    user_id = db.Column(db.String(50))
    business_id = db.Column(db.String(50))
    stars = db.Column(db.Integer)
    useful = db.Column(db.Integer)
    funny = db.Column(db.Integer)
    cool = db.Column(db.Integer)
    text = db.Column(db.Text)
    date = db.Column(db.String(50))

    def __init__(self, review_id, user_id, business_id, stars, useful, funny, cool, text, date):
        self.review_id = review_id
        self.user_id = user_id
        self.business_id = business_id
        self.stars = stars
        self.useful = useful
        self.funny = funny
        self.cool = cool
        self.text = text
        self.date = date

class Tip(db.Model):
    __tablename__ = 'tip'
    count_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String(50))
    business_id = db.Column(db.String(50))
    text = db.Column(db.Text)
    date = db.Column(db.String(50))
    compliment_count = db.Column(db.Integer)

    def __init__(self, user_id, business_id, text, date, compliment_count):
        self.user_id = user_id
        self.business_id = business_id
        self.text = text
        self.date = date
        self.compliment_count = compliment_count

class Business(db.Model):
    __tablename__ = 'business'
    business_id = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(50))
    address = db.Column(db.String(50))
    city = db.Column(db.String(50))
    state = db.Column(db.String(50))
    postal_code = db.Column(db.String(50))
    latitude = db.Column(db.Float)
    longitude = db.Column(db.Float)
    stars = db.Column(db.Float)
    review_count = db.Column(db.Integer)
    is_open = db.Column(db.Integer)
    business_accepts_credit_cards = db.Column(db.String(10))
    bike_parking = db.Column(db.String(10))
    good_for_kids = db.Column(db.String(10))
    business_parking = db.Column(db.String(100))
    by_appointment_only = db.Column(db.String(10))
    restaurants_price_range2 = db.Column(db.String(10))
    categories = db.Column(db.String(100))
    monday = db.Column(db.String(20))
    tuesday = db.Column(db.String(20))
    wednesday = db.Column(db.String(20))
    thursday = db.Column(db.String(20))
    friday = db.Column(db.String(20))
    saturday = db.Column(db.String(20))
    sunday = db.Column(db.String(20))

    def __init__(self, business_id, name, address, city, state, postal_code, latitude, longitude, stars, review_count, is_open, business_accepts_credit_cards, bike_parking, good_for_kids, business_parking, by_appointment_only, restaurants_price_range2, categories, monday, tuesday, wednesday, thursday, friday, saturday, sunday):
        self.business_id = business_id
        self.name = name
        self.address = address
        self.city = city
        self.state = state
        self.postal_code = postal_code
        self.latitude = latitude
        self.longitude = longitude
        self.stars = stars
        self.review_count = review_count
        self.is_open = is_open
        self.business_accepts_credit_cards = business_accepts_credit_cards
        self.bike_parking = bike_parking
        self.good_for_kids = good_for_kids
        self.business_parking = business_parking
        self.by_appointment_only = by_appointment_only
        self.restaurants_price_range2 = restaurants_price_range2
        self.categories = categories
        self.monday = monday
        self.tuesday = tuesday
        self.wednesday = wednesday
        self.thursday = thursday
        self.friday = friday
        self.saturday = saturday
        self.sunday = sunday
        
class User(db.Model):
    __tablename__ = 'user'
    count_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String(50))
    name = db.Column(db.String(50))
    review_count = db.Column(db.Integer)
    yelping_since = db.Column(db.String(50))
    useful = db.Column(db.Integer)
    funny = db.Column(db.Integer)
    cool = db.Column(db.Integer)
    elite = db.Column(db.String(200))
    friends = db.Column(db.Text)
    fans = db.Column(db.Integer)
    average_stars = db.Column(db.Float)
    compliment_hot = db.Column(db.Integer)
    compliment_more = db.Column(db.Integer)
    compliment_profile = db.Column(db.Integer)
    compliment_cute = db.Column(db.Integer)
    compliment_list = db.Column(db.Integer)
    compliment_note = db.Column(db.Integer)
    compliment_plain = db.Column(db.Integer)
    compliment_cool = db.Column(db.Integer)
    compliment_funny = db.Column(db.Integer)
    compliment_writer = db.Column(db.Integer)
    compliment_photos = db.Column(db.Integer)

    def __init__(self, user_id, name, review_count, yelping_since, useful, funny, cool, elite, friends, fans, average_stars, compliment_hot, compliment_more, compliment_profile, compliment_cute, compliment_list, compliment_note, compliment_plain, compliment_cool, compliment_funny, compliment_writer, compliment_photos):
        self.user_id = user_id
        self.name = name
        self.review_count = review_count
        self.yelping_since = yelping_since
        self.useful = useful
        self.funny = funny
        self.cool = cool
        self.elite = elite
        self.friends = friends
        self.fans = fans
        self.average_stars = average_stars
        self.compliment_hot = compliment_hot
        self.compliment_more = compliment_more
        self.compliment_profile = compliment_profile 
        self.compliment_cute = compliment_cute
        self.compliment_list = compliment_list
        self.compliment_note = compliment_note
        self.compliment_plain = compliment_plain
        self.compliment_cool = compliment_cool
        self.compliment_funny = compliment_funny
        self.compliment_writer = compliment_writer
        self.compliment_photos = compliment_photos


# Routes #####
@app.route('/createcheckin')
def createCheckin():
    print('==inserting checkin==')
    data_file = './data/yelp_dataset/checkin.json'
    db.create_all()
    with open(data_file) as f:
        line = f.readline()
        i = 0
        while line and i < 10000:
            info = json.loads(line)
            checkin = Checkin(business_id=info['business_id'], date=info['date'])
            db.session.add(checkin)
            line = f.readline()
            i = i + 1
    db.session.commit()
    return 'createcheckin'

@app.route('/createreview')
def createReview():
    print('==inserting review==')
    data_file = './data/yelp_dataset/review.json'
    db.create_all()
    with open(data_file) as f:
        line = f.readline()
        i = 0
        while line and i < 10000:
            info = json.loads(line)
            review = Review(review_id=info['review_id'], user_id=info['user_id'], business_id=info['business_id'], stars=info['stars'], useful=info['useful'], funny=info['funny'], cool=info['cool'], text=info['text'], date=info['date'])
            db.session.add(review)
            line = f.readline()
            i = i + 1
    db.session.commit()
    return 'createreview'

@app.route('/createtip')
def createTip():
    print('==inserting tip==')
    data_file = './data/yelp_dataset/tip.json'
    db.create_all()
    with open(data_file) as f:
        line = f.readline()
        i = 0
        while line and i < 10000:
            info = json.loads(line)
            tip = Tip(user_id=info['user_id'], business_id=info['business_id'], text=info['text'], date=info['date'], compliment_count=info['compliment_count'])
            db.session.add(tip)
            line = f.readline()
            i = i + 1
    db.session.commit()
    return 'createtip'

@app.route('/createbusiness')
def createBusiness():
    print('==inserting tip==')
    data_file = './data/yelp_dataset/business.json'
    db.create_all()
    with open(data_file) as f:
        line = f.readline()
        i = 0
        while line and i < 10000:
            info = json.loads(line)
            attributes = info['attributes']
            if attributes is None:
                attributes = {'BusinessAcceptsCreditCards': 'False', 'BikeParking': 'False', 'GoodForKids': 'False', 'BusinessParking': 'False', 'ByAppointmentOnly': 'False', 'RestaurantsPriceRange2': 'False'}
            else:
                attributes.setdefault('BusinessAcceptsCreditCards', False)
                attributes.setdefault('BikeParking', False)
                attributes.setdefault('GoodForKids', False)
                attributes.setdefault('BusinessParking', False)
                attributes.setdefault('ByAppointmentOnly', False)
                attributes.setdefault('RestaurantsPriceRange2', False)
            
            hours = info['hours']
            if hours is None:
                hours = {'Monday': '', 'Tuesday': '', 'Wednesday': '', 'Thursday': '', 'Friday': '', 'Saturday': '', 'Sunday': ''}
            else:
                hours.setdefault('Monday', '')
                hours.setdefault('Tuesday', '')
                hours.setdefault('Wednesday', '')
                hours.setdefault('Thursday', '')
                hours.setdefault('Friday', '')
                hours.setdefault('Saturday', '')
                hours.setdefault('Sunday', '')
            business = Business(business_id=info['business_id'], name=info['name'], address=info['address'], city=info['city'], state=info['state'], postal_code=info['postal_code'], latitude=info['latitude'], longitude=info['longitude'], stars=info['stars'], review_count=info['review_count'], is_open=info['is_open'], business_accepts_credit_cards=attributes['BusinessAcceptsCreditCards'], bike_parking=attributes['BikeParking'], good_for_kids=attributes['GoodForKids'], business_parking=attributes['BusinessParking'], by_appointment_only=attributes['ByAppointmentOnly'], restaurants_price_range2=attributes['RestaurantsPriceRange2'], categories=info['categories'], monday=hours['Monday'], tuesday=hours['Tuesday'], wednesday=hours['Wednesday'], thursday=hours['Thursday'], friday=hours['Friday'], saturday=hours['Saturday'], sunday=hours['Sunday'])
            db.session.add(business)
            line = f.readline()
            i = i + 1
    db.session.commit()
    return 'createbusiness'

@app.route('/createuser')
def createUser():
    print('==inserting tip==')
    data_file = './data/yelp_dataset/user.json'
    db.create_all()
    with open(data_file) as f:
        line = f.readline()
        i = 0
        while line and i < 10000:
            info = json.loads(line)
            user = User(user_id=info['user_id'], name=info['name'], review_count=info['review_count'], yelping_since=info['yelping_since'], useful=info['useful'], funny=info['funny'], cool=info['cool'], elite=info['elite'], friends=info['friends'], fans=info['fans'], average_stars=info['average_stars'], compliment_hot=info['compliment_hot'], compliment_more=info['compliment_more'], compliment_profile=info['compliment_profile'], compliment_cute=info['compliment_cute'], compliment_list=info['compliment_list'], compliment_note=info['compliment_note'], compliment_plain=info['compliment_plain'], compliment_cool=info['compliment_cool'], compliment_funny=info['compliment_funny'], compliment_writer=info['compliment_writer'], compliment_photos=info['compliment_photos'])
            db.session.add(user)
            line = f.readline()
            i = i + 1
    db.session.commit()
    return 'createuser'

if __name__ == '__main__':
    app.run(debug=True)
